USE [SGLDBMP01]
GO

/*					############### 06. Table: [TicketStatus] ###############					*/

DECLARE @TABLE_NAME		VARCHAR(50);
SET		@TABLE_NAME		= 'TicketStatus';

DECLARE @TABLE_SCHEMA	VARCHAR(5);
SET		@TABLE_SCHEMA	= 'dbo';

IF (
	EXISTS (
		SELECT	[TABLE_NAME] 
		FROM	SGLDBMP01.INFORMATION_SCHEMA.TABLES
		WHERE	TABLE_NAME		= @TABLE_NAME
			AND	TABLE_SCHEMA	= @TABLE_SCHEMA
			)
	)
	-- Condition satisfied table with this name already exists in this Database
	PRINT 'Table : ' + @TABLE_NAME + ', with the name already exists in the database.'
ELSE
	BEGIN

		CREATE TABLE dbo.[TicketStatus] (
			  TicketStatusId	BIGINT			NOT NULL IDENTITY(1, 1)
			, TicketId			BIGINT			NOT NULL
			, StatusCodeId		BIGINT			NOT NULL
			, DateUpdated		DATETIME		NOT NULL );
	
		-- Set TicketStatusId as the Primary Key for the table dbo.[TicketStatus]
		ALTER TABLE dbo.[TicketStatus] 
			ADD CONSTRAINT PKey_TicketStatus_TicketStatusId PRIMARY KEY (TicketStatusId);			

		-- Set TicketId as the Foreign Key (from dbo.Ticket) for the table dbo.TicketStatus
		ALTER TABLE dbo.[TicketStatus] 
			ADD CONSTRAINT FKey_TicketStatus_TicketId_TicketId FOREIGN KEY (TicketId)
			REFERENCES dbo.[Ticket](TicketId);

		-- Set StatusCodeId as the Foreign Key (from dbo.StatusCode) for the table dbo.TicketStatus
		ALTER TABLE dbo.[TicketStatus] 
			ADD CONSTRAINT FKey_TicketStatus_StatusCodeId_StatusCodeId FOREIGN KEY (StatusCodeId)
			REFERENCES dbo.[StatusCode](StatusCodeId);

		-- By default the current DateTime will be added to the row
		ALTER TABLE dbo.[TicketStatus] 
			ADD CONSTRAINT DefaultConstraint_TicketStatus_DateUpdated
			DEFAULT GETDATE() FOR DateUpdated;

		IF (
			EXISTS (
				SELECT	1 
				FROM	sys.tables
				WHERE	name = @TABLE_NAME
					AND type = 'U'
					)
			)
			PRINT 'Table : ' + @TABLE_NAME + ', created successfully'
		ELSE
			PRINT 'Table : ' + @TABLE_NAME + ', creation failed, kindly check the issue'
	END

/*	Output (01): Table : TicketStatus, created successfully
	Output (02): Table : TicketStatus, with the name already exists in the database.
	Output (03): 
	Msg 2705, Level 16, State 4, Line 49
	Column names in each table must be unique. Column name 'TicketId' in table 'dbo.TicketStatus' is specified more than once.
*/